/*
 * mysql> CREATE TABLE java_objects (
 * id INT AUTO_INCREMENT,
 * name varchar(128),
 * object_value BLOB,
 * primary key (id));
 **/

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class DB {
    static final String WRITE_OBJECT_SQL = "REPLACE INTO Data VALUES (?, ?,?)";

    static final String READ_OBJECT_SQL = "SELECT Data.Array FROM Data WHERE Data.Key = ?";

    public static Connection getConnection() throws Exception {
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://www.bombossaosebastiao.org:3306/bombossa_SDTP2";
        String username = "bombossa_sdtp2";
        String password = "sdtp2";
        Class.forName(driver);
        //Connection conn = DriverManager.getConnection(url, username, password);
        Connection conn = DriverManager.getConnection("jdbc:mysql://www.bombossaosebastiao.org:3306/bombossa_SDTP2?user=bombossa_sdtp2&password=sdtp2&useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&autoDeserialize=true&jdbcCompliantTruncation=false");
        return conn;
    }

    public static int writeJavaObject(Connection conn,int key ,Object object) throws Exception {
        String className = object.getClass().getName();
        PreparedStatement pstmt = conn.prepareStatement(WRITE_OBJECT_SQL);
        

        // set input parameters
        pstmt.setInt(1, key);
        pstmt.setString(2,className);
        pstmt.setObject(3, object);
        pstmt.executeUpdate();
        pstmt.close();
        System.out.println("writeJavaObject: done serializing: " + className + " key:"+ key);
        return key;
    }

    public static Object readJavaObject(Connection conn, int id) throws Exception {
        PreparedStatement pstmt = conn.prepareStatement(READ_OBJECT_SQL);
        pstmt.setInt(1, id);
        ResultSet rs = pstmt.executeQuery();
        rs.next();
        Object object = rs.getObject(1);
        String className = object.getClass().getName();
        rs.close();
        pstmt.close();
        System.out.println("readJavaObject: done de-serializing: " +className + " key: "+id);
        return object;
    }

    public static void main(String args[])throws Exception {
        Connection conn = null;
        try {
            conn = getConnection();
            System.out.println("conn=" + conn);
            conn.setAutoCommit(false);
            List<Object> list = new ArrayList<Object>();
            list.add("This is a short string.");
            list.add(new Integer(1234));
            list.add(new Date());

            int key = writeJavaObject(conn,11,list);
            conn.commit();
            System.out.println("Serialized objectID => " + key);
            List listFromDatabase = (List) readJavaObject(conn, key); 
            //System.out.println(listFromDatabase.getClass().getName());
            System.out.println("[After De-Serialization] list=" + listFromDatabase);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            conn.close();
        }
    }
}
